<?php
if (!defined('ABSPATH')) {
    exit();
}
if (!class_exists('CMC_Coins')) {
class CMC_Coins extends PW_DB
{

    /**
     * Get things started
     *
     * @access  public
     * @since   1.0
     */
    public $table_name;
    public $primary_key;
    public $version;
    public function __construct()
    {

        global $wpdb;

        $this->table_name = $wpdb->base_prefix . CMC_DB;
        $this->primary_key = 'id';
        $this->version = '1.0';

        //    $this->cmc_refresh_database();
    }

    /**
     * Get columns and formats
     *
     * @access  public
     * @since   1.0
     */
    public function get_columns()
    {

        return array(
            'id' => '%d',
            'coin_id' => '%s',
            'name' => '%s',
            'symbol' => '%s',
            'price' => '%f',
            'percent_change_24h' => '%f',
            'percent_change_1y' => '%f',
            'percent_change_30d' => '%f',
            'percent_change_7d' => '%f',
            'market_cap' => '%f',
            'total_volume' => '%f',
            'circulating_supply' => '%d',
            'weekly_price_data' => '%s',
            'weekly_price_data' => '%s',
            'coin_category' => '%s',
            'ath' => '%f',
            'high_24h' => '%f',
            'low_24h' => '%f',
            'ath_change_percentage' => '%f',
            'coin_status' => '%s',
        );
    }

    /**
     *
     * This is a wrap_up function for cryptocurrency search addon.
     *
     */
    public function get_coins_listdata($args = array())
    {

        return $this->get_coins($args);

    }

    public function cmc_insert($coins_data)
    {
        if (is_array($coins_data) && count($coins_data) >= 1) {

            return $this->wp_insert_rows($coins_data, $this->table_name, true, 'coin_id');
        }
    }


    public function get_total_coins_count()
    {
        global $wpdb;
        // Count rows where coin_status is not 'disable' using prepared statement
        $query = $wpdb->prepare(
            "SELECT COUNT(*) FROM {$this->table_name} WHERE coin_status != %s",
            'disable'
        );
        return $wpdb->get_var($query); // Returns the count as an integer
    }


    /**
     * This function is used to insert coin's weeklydata.
     *
     * @param array associative array with coin_id and related weeklydata
     *
     * @return int number of affected/inserted rows.
     *
     */
    public function cmc_weekly_data_insert($coins_data)
    {
        if (is_array($coins_data) && count($coins_data) > 1) {
            global $wpdb;
            
            // Build the CASE statement using proper prepared statements
            $case_parts = array();
            $coin_ids = array();
            $placeholders = array();
            
            foreach ($coins_data as $coin) {
                if ($coin['weekly_price_data'] != null) {
                    $chart_data = serialize($coin['weekly_price_data']);
                } else {
                    $chart_data = 'N/A';
                }
                
                $case_parts[] = "WHEN `coin_id` = %s THEN %s";
                $coin_ids[] = $coin['coin_id'];
                $placeholders[] = $chart_data;
            }
            
            // Build the complete query with proper placeholders
            $case_statement = implode(' ', $case_parts);
            $in_placeholders = implode(',', array_fill(0, count($coin_ids), '%s'));
            
            $query = $wpdb->prepare(
                "UPDATE {$this->table_name} SET `weekly_price_data` = CASE {$case_statement} END WHERE `coin_id` IN ({$in_placeholders})",
                array_merge($placeholders, $coin_ids)
            );
            
            $result = $wpdb->query($query);
            
            return $result;
        }
    }

    /**
     * Retrieve orders from the database
     *
     * @access  public
     * @since   1.0
     * @param   array $args
     * @param   bool  $count  Return only the total number of results found (optional)
     * @return array weeklyprice data for the coin_id passed as argument
     */
    public function get_coins_weeky_price($args = array(), $count = false)
    {
        global $wpdb;

        $defaults = array(
            'number' => 20,
            'offset' => 0,
            'coin_id' => '',
            'status' => '',
            'orderby' => 'id',
            'order' => 'ASC',
        );

        // Sanitize and escape user input from a form
        $args = wp_parse_args($args, $defaults);

        // Sanitize a number provided in $_POST['number_input'] using absint()
        $args['number'] = absint($args['number']);

        if ($args['number'] < 1) {
            $args['number'] = 999999999999;
        }

        $where = '';

        if (!empty($args['coin_id'])) {
            if (empty($where)) {
                $where .= " WHERE";
            } else {
                $where .= " AND";
            }
            // Use proper prepared statements for database queries
            if (is_array($args['coin_id'])) {
                $placeholders = implode(',', array_fill(0, count($args['coin_id']), '%s'));
                $where .= $wpdb->prepare(" `coin_id` IN({$placeholders}) ", $args['coin_id']);
            } else {
                $where .= $wpdb->prepare(" `coin_id` = %s ", $args['coin_id']);
            }
        }

        $args['orderby'] = !array_key_exists($args['orderby'], $this->get_columns()) ? $this->primary_key : $args['orderby'];

        if ('total' === $args['orderby']) {
            $args['orderby'] = 'total+0';
        } else if ('subtotal' === $args['orderby']) {
            $args['orderby'] = 'subtotal+0';
        }

        $cache_key = (true === $count) ? md5('cmc_coins_weekly_count' . serialize($args)) : md5('cmc_coins_weekly_' . serialize($args));

        $results = false;

        if (true === $count) {
            // Sanitize and escape user input for a database query and use $wpdb->prepare() for insertion
            $results = absint($wpdb->get_var($wpdb->prepare("SELECT COUNT(%d) FROM %s %s;", $this->primary_key, $this->table_name, $where)));
        } else {
            // Sanitize and escape user input for a database query and use $wpdb->prepare() for insertion
            $results = $wpdb->get_results(
                $wpdb->prepare(
                    "SELECT coin_id,weekly_price_data FROM %s %s ORDER BY %s %s LIMIT %d, %d;",
                    $this->table_name,
                    $where,
                    $args['orderby'],
                    $args['order'],
                    absint($args['offset']),
                    absint($args['number'])
                )
            );
        }

        return $results;
    }

    /**
     * Get default column values
     *
     * @access  public
     * @since   1.0
     */
    public function get_column_defaults()
    {
        return array(
            'coin_id' => '',
            'name' => '',
            'symbol' => '',
            'price' => '',
            'percent_change_24h' => '',
            'percent_change_1y' => '',
            'percent_change_30d' => '',
            'percent_change_7d' => '',
            'market_cap' => '',
            'total_volume' => '',
            'circulating_supply' => '',
            'weekly_price_data' => '',
            'last_updated' => date('Y-m-d H:i:s'),
            'coin_status' => 'enable',
            'high_24h ' => '',
            'low_24h ' => '',
            'ath ' => '',
            'ath_change_percentage ' => '',
            'ath_date ' => '',
        );
    }

    public function coin_exists_by_id($coin_ID)
    {
        global $wpdb;

        // Sanitize and escape user input for a database query and use $wpdb->prepare() for insertion
        $coin_ID = sanitize_text_field($coin_ID);
        $count = $wpdb->get_var($wpdb->prepare("SELECT COUNT(*) FROM $this->table_name WHERE coin_id ='%s'", $coin_ID));
        if ($count == 1) {
            return true;
        } else {
            return false;
        }
    }

    /**
     * Check if the coin is enabled
     *
     * @param string $coin_id The ID of the coin to check
     * @return bool|null True if the coin is enabled, false if disabled, null if not found
     */
    public function is_coin_enabled($coin_id)
    {
        global $wpdb;

        // Sanitize and escape user input for a database query and use $wpdb->prepare() for insertion
        $coin_id = sanitize_text_field($coin_id); // Sanitize the coin ID as a string
        $response = $wpdb->get_var($wpdb->prepare("SELECT coin_status FROM $this->table_name WHERE coin_id = %s", $coin_id));

        if ($response === null) {
            return null; // Return null if the coin is not found
        } else {
            return $response === 'enable' ? true : false; // Return true if the coin is enabled, false if disabled
        }
    }

    /**
     * Retrieve the logo URL for the specific coin
     *
     * @param string $coin_id The ID of the coin to retrieve the logo for
     * @return string|null The logo URL if found, null if not found
     */
    public function get_coin_logo($coin_id = null)
    {
        // Sanitize and escape user input for a database query and use $wpdb->prepare() for insertion
        $coin_id = sanitize_text_field($coin_id);

        if (!empty($coin_id)) {
            global $wpdb;
            // Prepare the SQL query with $wpdb->prepare() to prevent SQL injection
            $query = $wpdb->prepare("SELECT * FROM $this->table_name WHERE coin_id = %s LIMIT 1", $coin_id);

            // Retrieve the logo URL from the database
            $logo = $wpdb->get_row($query, ARRAY_A);
            return $logo;
        } else {
            throw new Exception("one argument 'coin_id' expected. Null given,");
        }
    }

    /**
     * Retrieve orders from the database
     *
     * @access  public
     * @since   1.0
     * @param   array $args
     * @param    array $selection name of column return from database. (optional)
     * @param   bool  $count  Return only the total number of results found (optional)
     */
    public function get_coins($args = array(), $selection = null, $count = false)
    {

        global $wpdb;
        $defaults = array(
            'number' => 20,
            'offset' => 0,
            'coin_search' => '',
            'coin_category' => '',
            'coin_id' => '',
            'status' => '',
            'email' => '',
            'weekly_price_data' => '',
            'orderby' => 'id',
            'order' => 'ASC',

        );
        $args = wp_parse_args($args, $defaults);
        if ($args['number'] < 1) {
            $args['number'] = 999999999999;
        }
        $cate = '"';
        $cate_len = strlen($args['coin_category']);
        $two_days_ago = date('Y-m-d H:i:s', strtotime('-2 day'));
        $where = '';
        // specific referrals
        if (!empty($args['id'])) {
            $order_ids = is_array($args['id']) ? implode(',', array_map('absint', $args['id'])) : absint($args['id']);
            $where .= "WHERE `id` IN( {$order_ids} ) ";
        }
        if (!empty($args['coin_id'])) {
            $where .= empty($where) ? " WHERE" : " AND";
            if (is_array($args['coin_id'])) {
                $placeholders = implode(',', array_fill(0, count($args['coin_id']), '%s'));
                $where .= $wpdb->prepare(" `coin_id` IN({$placeholders}) ", $args['coin_id']);
            } else {
                $where .= $wpdb->prepare(" `coin_id` = %s ", $args['coin_id']);
            }
        }
        if (!empty($args['coin_category'])) {
            $where .= empty($where) ? " WHERE" : " AND";
            if (is_array($args['coin_category'])) {
                $category_patterns = array();
                foreach ($args['coin_category'] as $category) {
                    // Escape and sanitize the category value for use in REGEXP
                    $category = preg_quote(sanitize_text_field($category), '/');
                    // Use a properly escaped and parameterized REGEXP pattern
                    $category_patterns[] = $wpdb->prepare(
                        "`coin_category` REGEXP %s",
                        '.*;s:[0-9]+:"' . $category . '".*'
                    );
                }
                $where .= " (" . implode(" OR ", $category_patterns) . ") ";
            } else {
                // Escape and sanitize the category value for use in REGEXP
                $category = preg_quote(sanitize_text_field($args['coin_category']), '/');
                $where .= $wpdb->prepare(
                    "`coin_category` REGEXP %s",
                    '.*;s:[0-9]+:"' . $category . '".*'
                );
            }
        }
        if (!empty($args['coin_search'])) {
            $coin_search = '%' . $wpdb->esc_like($args['coin_search']) . '%';
            $where .= empty($where) ? " WHERE" : " AND";
            $where .= $wpdb->prepare(" (`name` LIKE %s OR `symbol` LIKE %s)", $coin_search, $coin_search);
        }
        if (empty($where)) {
            $where .= " WHERE";
        } else {
            $where .= " AND";
        }
        $where .= " `coin_status` != 'disable'";
        if (empty($where)) {
            $where .= " WHERE";
        } else {
            $where .= " AND";
        }
        $where .= $wpdb->prepare(" `last_updated` >= %s", $two_days_ago);
        if (empty($where)) {
            $where .= " WHERE";
        } else {
            $where .= " AND";
        }
        $where .= " `price` IS NOT NULL";
        $args['orderby'] = !array_key_exists($args['orderby'], $this->get_columns()) ? $this->primary_key : $args['orderby'];
        if ('total' === $args['orderby']) {
            $args['orderby'] = 'total+0';
        } else if ('subtotal' === $args['orderby']) {
            $args['orderby'] = 'subtotal+0';
        }
        $cache_key = (true === $count) ? md5('cmc_coins_count' . serialize($args)) : md5('cmc_coins_' . serialize($args));

        $results = wp_cache_get($cache_key, 'coins');

        
        if (false === $results) {
            if (true === $count) {
                $results = absint($wpdb->get_var($wpdb->prepare("SELECT COUNT({$this->primary_key}) FROM {$this->table_name} {$where};")));
            } else {
                $selection = $selection == "" ? '*' : '`' . implode("`,`", array_map(array($wpdb, 'esc_sql'), $selection)) . '`';
                $results = $wpdb->get_results($wpdb->prepare("SELECT {$selection} FROM {$this->table_name} {$where} ORDER BY {$args['orderby']} {$args['order']},`total_volume` DESC LIMIT %d OFFSET %d", absint($args['number']), absint($args['offset'])));
            }
            wp_cache_set($cache_key, $results, 'coins', 3600);

        }

        return $results;
    }

    /**
     * Retrieve orders from the database
     *
     * @access  public
     * @since   1.0
     * @param   array $args
     * @param   bool  $count  Return only the total number of results found (optional)
     */

    public function get_cmc_coins_listdata($args = array(), $count = false)
    {
        global $wpdb;

        // Sanitize and escape user input from a form
        $args = wp_parse_args($args, array(
            'number' => 20,
            'offset' => 0,
            'coin_id' => '',
            'status' => '',
            'email' => '',
            'orderby' => 'id',
            'order' => 'ASC',
        ));

        // Sanitize a number provided in $_POST['number_input'] using absint()
        $args['number'] = absint($args['number']);
        if ($args['number'] < 1) {
            $args['number'] = 999999999999;
        }

        $where = '';

        // specific referrals
        if (!empty($args['id'])) {
            // Sanitize and escape HTML input from $_POST['html_input'] for safe output in HTML
            $order_ids = is_array($args['id']) ? implode(',', array_map('absint', $args['id'])) : absint($args['id']);
            $where .= "WHERE `id` IN( {$order_ids} ) ";
        }

        if (!empty($args['coin_id'])) {
            if (empty($where)) {
                $where .= " WHERE";
            } else {
                $where .= " AND";
            }

            if (is_array($args['coin_id'])) {
                $placeholders = implode(',', array_fill(0, count($args['coin_id']), '%s'));
                $where .= $wpdb->prepare(" `coin_id` IN({$placeholders}) ", $args['coin_id']);
            } else {
                $where .= $wpdb->prepare(" `coin_id` = %s ", $args['coin_id']);
            }
        }

        if (empty($where)) {
            $where .= " WHERE";
        } else {
            $where .= " AND";
        }
        $where .= " `coin_status` != 'disable'";

        // Sanitize and escape user input for a database query and use $wpdb->prepare() for insertion
        $args['orderby'] = !array_key_exists($args['orderby'], $this->get_columns()) ? $this->primary_key : $args['orderby'];

        if ('total' === $args['orderby']) {
            $args['orderby'] = 'total+0';
        } else if ('subtotal' === $args['orderby']) {
            $args['orderby'] = 'subtotal+0';
        }

        $results = false;

        if (true === $count) {
            // Sanitize and escape user input for a database query and use $wpdb->prepare() for insertion
            $results = absint($wpdb->get_var($wpdb->prepare("SELECT COUNT(%s) FROM {$this->table_name} {$where};", $this->primary_key)));
        } else {
            // Sanitize and escape user input for a database query and use $wpdb->prepare() for insertion
            $results = $wpdb->get_results(
                $wpdb->prepare(
                    "SELECT name,price,symbol,coin_id FROM {$this->table_name} {$where} ORDER BY {$args['orderby']} {$args['order']} LIMIT %d, %d;",
                    absint($args['offset']),
                    absint($args['number'])
                )
            );
        }
        return $results;
    }

    /**
     * Retrieves top changing coins based on provided arguments.
     *
     * @param array $args   (Optional) Array of arguments for filtering the coins.
     * @param bool  $count  (Optional) Whether to count the results.
     * @return array|object|null Array of results or null if no results found.
     */
    public function get_top_changers_coins($args = array(), $count = false)
    {
        global $wpdb;

        // Default arguments
        $defaults = array(
            'number' => 20,
            'offset' => 0,
            'coin_id' => '',
            'status' => '',
            'email' => '',
            'orderby' => 'market_cap',
            'order' => 'DESC',
            'volume' => 50000,
        );

        // Merge provided arguments with defaults
        $args = wp_parse_args($args, $defaults);

        // Sanitize and escape the number input using absint()
        $args['number'] = absint($args['number']);

        // Set up the WHERE clause
        $where = '';

        // Specific referrals
        if (!empty($args['volume'])) {
            $where .= $wpdb->prepare("WHERE `total_volume` > %f", $args['volume']);
        }

        if (!empty($args['coin_id'])) {
            if (empty($where)) {
                $where .= " WHERE";
            } else {
                $where .= " AND";
            }

            if (is_array($args['coin_id'])) {
                $placeholders = implode(',', array_fill(0, count($args['coin_id']), '%s'));
                $where .= $wpdb->prepare(" `coin_id` IN({$placeholders}) ", $args['coin_id']);
            } else {
                $where .= $wpdb->prepare(" `coin_id` = %s ", $args['coin_id']);
            }
        }

        if (empty($where)) {
            $where .= " WHERE";
        } else {
            $where .= " AND";
        }

        // Add condition for coin_status
        $where .= " `coin_status` != 'disable'";

        // Determine the orderby value
        $args['orderby'] = !array_key_exists($args['orderby'], $this->get_columns()) ? $this->primary_key : $args['orderby'];

        if ('total' === $args['orderby']) {
            $args['orderby'] = 'total+0';
        } else if ('subtotal' === $args['orderby']) {
            $args['orderby'] = 'subtotal+0';
        }

        // Prepare and execute the database query
        $results = $wpdb->get_results(
            $wpdb->prepare(
                "SELECT * FROM {$this->table_name} {$where} ORDER BY {$args['orderby']} {$args['order']} LIMIT %d, %d;",
                absint($args['offset']),
                absint($args['number'])
            )
        );

        return $results;
    }

    /**
     * Insert rows into the database table with proper sanitization and escaping
     *
     * @param array  $row_arrays   Array of rows to be inserted
     * @param string $wp_table_name Name of the WordPress table
     * @param bool   $update       Whether to update the existing rows
     * @param string $primary_key  Primary key for the table
     * @return bool                True on success, false on failure
     */
    public function wp_insert_rows($row_arrays, $wp_table_name, $update = false, $primary_key = null)
    {
        global $wpdb;
        // Table names should be validated, not escaped with esc_sql
        // Only allow alphanumeric characters, underscores, and hyphens for table names
        $wp_table_name = preg_replace('/[^a-zA-Z0-9_-]/', '', $wp_table_name);

        // Setup arrays for Actual Values, and Placeholders
        $values = array();
        $place_holders = array();
        $query = "";
        $query_columns = "";
        $floatCols = array('price', 'percent_change_24h', 'percent_change_1y', 'percent_change_30d', 'percent_change_7d', 'market_cap', 'total_volume', 'circulating_supply', 'ath', 'ath_change_percentage', 'high_24h', 'low_24h');
        $query .= "INSERT INTO `{$wp_table_name}` (";

        foreach ($row_arrays as $count => $row_array) {
            foreach ($row_array as $key => $value) {
                if ($count == 0) {
                    if ($query_columns) {
                        $query_columns .= ", " . $key . "";
                    } else {
                        $query_columns .= "" . $key . "";
                    }
                }

                $values[] = $value;

                $symbol = "%s";
                if (is_numeric($value)) {
                    $symbol = "%d"; // Sanitizing the value as integer
                }

                if (in_array($key, $floatCols)) {
                    $symbol = "%.18f"; // Sanitizing the value as float
                }
                if (isset($place_holders[$count])) {
                    $place_holders[$count] .= ", '$symbol'";
                } else {
                    $place_holders[$count] = "( '$symbol'";
                }
            }
            // mind closing the GAP
            $place_holders[$count] .= ")";
        }

        $query .= " $query_columns ) VALUES ";

        $query .= implode(', ', $place_holders);

        if ($update) {
            $update = " ON DUPLICATE KEY UPDATE $primary_key=VALUES( $primary_key ),";
            $cnt = 0;
            foreach ($row_arrays[0] as $key => $value) {
                if ($cnt == 0) {
                    $update .= "$key=VALUES($key)";
                    $cnt = 1;
                } else {
                    $update .= ", $key=VALUES($key)";
                }
            }
            $query .= $update;
        }

        $sql = $wpdb->prepare($query, $values); // Sanitizing and escaping the query

        if ($wpdb->query($sql)) {
            return true;
        } else {
            return false;
        }
    }
    /**
     * This function will remove the coins from database if updated more than 1 day ago
     *
     * @param int Rows to be removed after this number. DEPERECIATED
     */
    public function cmc_refresh_db($rows = null)
    {
        global $wpdb;
        $table = $wpdb->get_var($wpdb->prepare("SHOW TABLES LIKE %s", $this->table_name));
        $date = date('Y-m-d h:m:s', strtotime("-2 days"));

        if ($table == $this->table_name) {
            // Sanitize and escape user input for a database query and use $wpdb->prepare() for insertion
            $wpdb->query($wpdb->prepare("DELETE FROM $this->table_name WHERE last_updated <= %s ", $date));
        }
    }
/**
 * This function will remove the coins from database if updated more than 1 day ago
 *
 * @param int Rows to be removed after this number. DEPERECIATED
 */
    public function cmc_refresh_database($rows = null)
    {

        global $wpdb;
        $table = $wpdb->get_var($wpdb->prepare("SHOW TABLES LIKE %s", $this->table_name));
        if ($table == $this->table_name) {
            $wpdb->query($wpdb->prepare("DELETE FROM $this->table_name WHERE last_updated <= %d ", strtotime('-1 day')));
        }

    }

    /**
     * Return the number of results found for a given query
     *
     * @param  array  $args
     * @return int
     */
    public function count($args = array())
    {
        return $this->get_coins($args, true);
    }

    /**
     * Create the table
     *
     * @access  public
     * @since   1.0
     */
    public function create_table()
    {
        global $wpdb;

        require_once ABSPATH . 'wp-admin/includes/upgrade.php';

        // IF NOT EXISTS - condition not required

        $sql = "CREATE TABLE " . $this->table_name . " (
		id bigint(20) NOT NULL AUTO_INCREMENT,
		coin_id varchar(30) NOT NULL UNIQUE,
		name varchar(30) NOT NULL,
		symbol varchar(20) NOT NULL,
		logo text(300),
		price decimal(30,18),
		percent_change_24h decimal(6,2) ,
		percent_change_1y decimal(20,2) ,
		percent_change_30d decimal(6,2) ,
		percent_change_7d decimal(6,2) ,
		market_cap decimal(24,2),
		total_volume decimal(24,2) ,
		circulating_supply varchar(250),
		weekly_price_data longtext NOT NULL,
		coin_status varchar(20) NOT NULL DEFAULT 'enable',
		coin_category longtext,
		last_updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
		high_24h decimal(30,18),
		low_24h decimal(30,18),
		ath decimal(30,18),
		ath_change_percentage decimal(20,6),
		ath_date TIMESTAMP NOT NULL,
        extradata longtext,
		PRIMARY KEY (id)
		) CHARACTER SET utf8 COLLATE utf8_general_ci;";

        dbDelta($sql);
        //$wpdb->query($sql);

        update_option($this->table_name . '_db_version', $this->version);
    }

    /**
     * Remove table linked to this database class file
     */
    public function drop_table()
    {
        global $wpdb;
        $wpdb->query("DROP TABLE IF EXISTS " . $this->table_name);
    }

}
}